unit ReportsForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.Grids, Vcl.DBGrids, Vcl.ExtCtrls,
  Vcl.ComCtrls, Vcl.StdCtrls, DBModule, Vcl.DBCtrls, System.DateUtils, UserUtils,
  frxClass, frxDBSet, frxGradient, frxExportCSV, frxExportPDF, frxExportXLS,
  fs_ipascal, System.StrUtils;

type
  TfmReports = class(TForm)
    GroupBox1: TGroupBox;
    rbExpensesList: TRadioButton;
    rbPaymentReceived: TRadioButton;
    rbOverDuePayment: TRadioButton;
    rbAccountStatement: TRadioButton;
    rbLostProfit: TRadioButton;
    GroupBox2: TGroupBox;
    Label3: TLabel;
    chkDateAll: TCheckBox;
    lblDateFrom: TLabel;
    dtDateFrom: TDateTimePicker;
    lblDateTo: TLabel;
    dtDateTo: TDateTimePicker;
    lblSelectBy: TLabel;
    chkSelectByAll: TCheckBox;
    dbcbSelectBy: TDBLookupComboBox;
    frxReport1: TfrxReport;
    frxDBReport: TfrxDBDataset;
    frxDBConfig: TfrxDBDataset;
    btnPrint: TButton;
    btnPreview: TButton;
    btnClose: TButton;
    rbInvoiceByPhotographer: TRadioButton;
    lblPhotographer: TLabel;
    dbcbPhotographer: TDBLookupComboBox;
    frxXLSExport1: TfrxXLSExport;
    frxPDFExport1: TfrxPDFExport;
    frxCSVExport1: TfrxCSVExport;
    chkPhotographerAll: TCheckBox;
    gbOverDueOptions: TGroupBox;
    rbOverDueList: TRadioButton;
    rbOverDueSummary: TRadioButton;
    rbOverDueDetails: TRadioButton;
    chkWithOverDueOnly: TCheckBox;
    chkShowPaidAmount: TCheckBox;
    rgSortOrder: TRadioGroup;
    rbMonthlyPayment: TRadioButton;
    RadioGroup1: TRadioGroup;
    procedure chkDateAllClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure chkSelectByAllClick(Sender: TObject);
    procedure rbExpensesListClick(Sender: TObject);
    procedure rbPaymentReceivedClick(Sender: TObject);
    procedure dbcbSelectByDropDown(Sender: TObject);
    procedure rbOverDuePaymentClick(Sender: TObject);
    procedure rbPaymentAgingClick(Sender: TObject);
    procedure rbAccountStatementClick(Sender: TObject);
    procedure dtDateToExit(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnPreviewClick(Sender: TObject);
    procedure btnPrintClick(Sender: TObject);
    function frxReport1UserFunction(const MethodName: string;
      var Params: Variant): Variant;
    procedure dtDateFromExit(Sender: TObject);
    procedure frxReport1GetValue(const VarName: string; var Value: Variant);
    procedure rbLostProfitClick(Sender: TObject);
    procedure rbInvoiceByPhotographerClick(Sender: TObject);
    procedure dbcbPhotographerDropDown(Sender: TObject);
    procedure chkPhotographerAllClick(Sender: TObject);
    procedure rbOverDueListClick(Sender: TObject);
    procedure rbOverDueDetailsClick(Sender: TObject);
    procedure rbOverDueSummaryClick(Sender: TObject);
    procedure rbMonthlyPaymentClick(Sender: TObject);
  private
    ExpensesList: TStringList;

    procedure SetSelectByCompany(Visible: boolean = True);
    procedure SetSelectByCategory(Visible: boolean = True);
    procedure SetDateVisible(Visible: boolean = True);
    procedure SetPhotographerVisible(Visible: boolean = True);
    procedure SetOverDueOptions(Visible: boolean = True);
    procedure PrepareReport;
    procedure PrepareExpensesListData;
    procedure PreparePaymentReceivedData;
    procedure PrepareOverDuePaymentData;
    procedure PrepareAccountStatementData;
    procedure PrepareInvoiceByPhotographerData;
    procedure PrepareLostProfitData;
    procedure PrepareMonthlyPaymentData;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fmReports: TfmReports;

implementation

{$R *.dfm}

procedure TfmReports.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmReports.btnPreviewClick(Sender: TObject);
begin
  PrepareReport;
  frxReport1.ShowReport();
end;

procedure TfmReports.btnPrintClick(Sender: TObject);
begin
  PrepareReport;
  frxReport1.PrepareReport();
  frxReport1.Print();
end;

procedure TfmReports.chkSelectByAllClick(Sender: TObject);
begin
  dbcbSelectBy.Visible := not chkSelectByAll.Checked;
  with dbcbSelectBy.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmReports.chkDateAllClick(Sender: TObject);
begin
  lblDateFrom.Visible := not chkDateAll.Checked;
  dtDateFrom.Visible := lblDateFrom.Visible;
  lblDateTo.Visible := lblDateFrom.Visible;
  dtDateTo.Visible := lblDateFrom.Visible;
end;

procedure TfmReports.chkPhotographerAllClick(Sender: TObject);
begin
  dbcbPhotographer.Visible := not chkPhotographerAll.Checked;
  with dbcbPhotographer.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmReports.dbcbPhotographerDropDown(Sender: TObject);
begin
  with dbcbPhotographer.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmReports.dbcbSelectByDropDown(Sender: TObject);
begin
  with dbcbSelectBy.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmReports.dtDateFromExit(Sender: TObject);
begin
  dtDateTo.Date := EndOfAMonth(YearOf(dtDateFrom.Date), MonthOf(dtDateFrom.Date));;
end;

procedure TfmReports.dtDateToExit(Sender: TObject);
begin
  if dtDateTo.Date < dtDateFrom.Date then
  begin
    dtDateTo.SetFocus;
    ShowMessage('Date To can''t less then Date From.');
  end;
end;

procedure TfmReports.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmReports);
end;

procedure TfmReports.FormCreate(Sender: TObject);
var
  Year, Month: Word;
begin
  ExpensesList := TStringList.Create;
  Year := YearOf(Date);
  Month := MonthOf(Date);
  dtDateFrom.Date := StartOfAMonth(Year, Month);
  dtDateTo.Date := EndOfAMonth(Year, Month);
  frxReport1.AddFunction('function FileExist(s: string): Boolean;');
  frxReport1.AddFunction('function StrReplace(s:string; p1:string; p2:string): string;');
  frxReport1.AddFunction('function ShortMonthNames(i: integer): string;');
  frxReport1.AddFunction('function LongMonthNames(i: integer): string;');
end;

procedure TfmReports.frxReport1GetValue(const VarName: string;
  var Value: Variant);
var
  VarName2: string;
  n: integer;
begin
  VarName2 := UpperCase(VarName);
  if LeftStr(VarName2, 3) = 'EXP' then
    n := StrToInt(RightStr(VarName2, 1));

  if VarName2 = 'TOTALINCOME' then
    with DataModule1.qrySQL do
    begin
      SQL.Clear;
      Params.Clear;
      SQL.Add('Select Sum(INVOICE.TOTAL_AMOUNT) as TotalIncome, Sum(INVOICE.AMOUNT_RECEIVED) as TotalReceived' +
              ' From INVOICE Where INVOICE_DATE>=:DateFrom And INVOICE_DATE<=:DateTo');
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
      if not Prepared then
        Prepare;
      Open;
      Value := 0;
      if RecordCount > 0 then
        Value := FieldByName('TotalIncome').AsFloat;
    end
  else if VarName2 = 'TOTALSALARY' then
    with DataModule1.qrySQL do
    begin
      SQL.Clear;
      Params.Clear;
      SQL.Add('Select Sum(PAY_SALARY.NET_PAY) as TotalSalary From PAY_SALARY ' +
              'Where PAY_SALARY."YEAR"=:Year');
      Params.ParamByName('Year').AsInteger := YearOf(dtDateFrom.Date);
      if not Prepared then
        Prepare;
      Open;
      Value := 0;
      if RecordCount > 0 then
        Value := FieldByName('TotalSalary').AsFloat;
    end
  else if VarName2 = 'EXPNAME1' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Names[0];
  end else if VarName2 = 'EXP1' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Values[ExpensesList.Names[0]]
    else
      Value := 0;
  end else if VarName2 = 'EXPNAME2' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Names[1];
  end else if VarName2 = 'EXP2' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Values[ExpensesList.Names[1]]
    else
      Value := 0;
  end else if VarName2 = 'EXPNAME3' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Names[2];
  end else if VarName2 = 'EXP3' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Values[ExpensesList.Names[2]]
    else
      Value := 0;
  end else if VarName2 = 'EXPNAME4' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Names[3];
  end else if VarName2 = 'EXP4' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Values[ExpensesList.Names[3]]
    else
      Value := 0;
  end else if VarName2 = 'EXPNAME5' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Names[4];
  end else if VarName2 = 'EXP5' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Values[ExpensesList.Names[4]]
    else
      Value := 0;
  end else if VarName2 = 'EXPNAME6' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Names[5];
  end else if VarName2 = 'EXP6' then begin
    if ExpensesList.Count > n then
      Value := ExpensesList.Values[ExpensesList.Names[5]]
    else
      Value := 0;
  end else if VarName2 = 'DATEFROM' then
    Value := dtDateFrom.Date
  else if VarName2 = 'PHOTOGRAPHERNAME' then
    begin
      if chkPhotographerAll.Checked then
        Value := 'All'
      else
        Value := dbcbPhotographer.Text;
    end
  else if VarName2 = 'DATETO' then
    Value := dtDateTo.Date;
end;

function TfmReports.frxReport1UserFunction(const MethodName: string;
  var Params: Variant): Variant;
begin
  if MethodName = 'FILEEXIST' then
    Result := FileExists(Params[0])
  else if MethodName = 'STRREPLACE' then
    Result := StringReplace(Params[0], Params[1], Params[2], [rfReplaceAll])
  else if MethodName = 'SHORTMONTHNAMES' then
    begin
      if Params[0] = varEmpty then
        Result := ''
      else
        Result := FormatSettings.ShortMonthNames[VarToInt(Params[0])];
    end
  else if MethodName = 'LONGMONTHNAMES' then
    begin
      if Params[0] = varEmpty then
        Result := ''
      else
        Result := FormatSettings.LongMonthNames[VarToInt(Params[0])];
    end;
end;

procedure TfmReports.rbAccountStatementClick(Sender: TObject);
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select CUSTOMERS.*, INVOICE.INVOICE_NO, INVOICE.INVOICE_DATE, ' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, ' +
            'INVOICE.DATE_OF_SHOOT, INVOICE."FOR", INVOICE.PUBLISH_ON, ' +
            'INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_REFERENCE, INVOICE.AMOUNT_RECEIVED ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Order By CUSTOMERS.COMPANY_NAME, INVOICE.DATE_OF_SHOOT, INVOICE.INVOICE_NO');
    Open;
  end;
  SetSelectByCompany();
  SetDateVisible();
  chkDateAll.Checked := True;
  SetPhotographerVisible(False);
  SetOverDueOptions(False);
end;

procedure TfmReports.PrepareAccountStatementData;
var
  tmpSQL: string;
begin
  with DataModule1.qryReport do
  begin
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    if not chkSelectByAll.Checked then
      SQL.Add('Where INVOICE.CUSTOMER_ID=:CustomerID ');
    if not chkDateAll.Checked then
      SQL.Add('And INVOICE.INVOICE_DATE>=:DateFrom ' +
              'And INVOICE.INVOICE_DATE <=:DateTo ');
    SQL.Add('Order By CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO, INVOICE.INVOICE_DUE_DATE');
    if Params.FindParam('DateFrom') <> nil then
    begin
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
    end;
    if Params.FindParam('CustomerID') <> nil then
      Params.ParamByName('CustomerID').AsInteger := VarToInt(dbcbSelectBy.KeyValue);
    if not Prepared then
      Prepare;
    if not Active then
      Open;
  end;
  frxReport1.LoadFromFile('AccountStatement.fr3');
end;

procedure TfmReports.SetSelectByCompany(Visible: boolean);
begin
  lblSelectBy.Visible := Visible;
  chkSelectByAll.Visible := Visible;
  dbcbSelectBy.Visible := not chkSelectByAll.Checked;
  lblSelectBy.Caption := '&Company';
  dbcbSelectBy.ListField := '';
  dbcbSelectBy.KeyField := '';
  dbcbSelectBy.ListSource := DataModule1.dsCustomers;
  dbcbSelectBy.ListField := 'COMPANY_NAME';
  dbcbSelectBy.KeyField := 'ID';
end;

procedure TfmReports.PrepareReport;
begin
  if rbExpensesList.Checked then
    PrepareExpensesListData
  else if rbPaymentReceived.Checked then
    PreparePaymentReceivedData
  else if rbOverDuePayment.Checked then
    PrepareOverDuePaymentData
  else if rbAccountStatement.Checked then
    PrepareAccountStatementData
  else if rbLostProfit.Checked then
    PrepareLostProfitData
  else if rbMonthlyPayment.Checked then
    PrepareMonthlyPaymentData
  else if rbInvoiceByPhotographer.Checked then
    PrepareInvoiceByPhotographerData;
end;

procedure TfmReports.rbInvoiceByPhotographerClick(Sender: TObject);
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select INVOICE.INVOICE_NO, CUSTOMERS.COMPANY_NAME, INVOICE.FOR_PRODUCT, INVOICE.INVOICE_DATE, ' +
            'INVOICE."FOR", INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, INVOICE.JOBSHEET_NO, ' +
            'EMPLOYEE.NAME, INVOICE.PHOTOGRAPHER1_ID, INVOICE.PAID, INVOICE.AMOUNT_RECEIVED ' +
            'From INVOICE Left Join EMPLOYEE On INVOICE.PHOTOGRAPHER1_ID = EMPLOYEE.ID Left Join ' +
            'CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID');
    Open;
  end;
  SetPhotographerVisible();
  SetDateVisible();
  chkDateAll.Checked := True;
  SetSelectByCategory(False);
  SetOverDueOptions(False);
end;

procedure TfmReports.PrepareInvoiceByPhotographerData;
var
  tmpSQL: string;
  Clause: string;
  FieldName: string;
begin
  with DataModule1.qryReport do
  begin
    case RadioGroup1.ItemIndex of
      0: FieldName := 'DATE_OF_SHOOT';
      1: FieldName := 'INVOICE_DATE';
      2: FieldName := 'PAYMENT_RECEIVED_DATE';
    end;
    Clause := 'Where ';
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    if not chkPhotographerAll.Checked then
    begin
      SQL.Add(Clause + 'INVOICE.PHOTOGRAPHER1_ID=:EmployeeID ');
      Clause := '';
    end;
    if not chkDateAll.Checked then
    begin
      if Clause = '' then Clause := ' And ';
      SQL.Add(Clause + 'INVOICE.' + FieldName + ' >=:DateFrom ' +
              'And INVOICE.' + FieldName + ' <=:DateTo');
      Clause := '';
    end;
    if chkWithOverDueOnly.Checked then
    begin
      if Clause = '' then Clause := ' And ';
      SQL.Add(Clause + 'Paid is null');
      Clause := '';
    end;

    SQL.Add('Order By Extract(year from INVOICE.' + FieldName + '), ' +
            'Extract(month from INVOICE.' + FieldName + '), ' +
            'INVOICE.PHOTOGRAPHER1_ID, ' +
            'INVOICE.INVOICE_NO');
    if Params.FindParam('DateFrom') <> nil then
    begin
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
    end;
    if Params.FindParam('EmployeeID') <> nil then
      Params.ParamByName('EmployeeID').AsInteger := VarToInt(dbcbPhotographer.KeyValue);
    if not Prepared then
      Prepare;
    if not Active then
      Open;
  end;
  case RadioGroup1.ItemIndex of
    0: frxReport1.LoadFromFile('InvoiceListByPhotographerShootingDate.fr3');
    1: frxReport1.LoadFromFile('InvoiceListByPhotographerInvoiceDate.fr3');
    2: frxReport1.LoadFromFile('InvoiceListByPhotographerShootingDate.fr3');
  end;

end;

procedure TfmReports.rbExpensesListClick(Sender: TObject);
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select EXPENSE_CATEGORY.NAME, EXPENSE_CATEGORY.DSC, EXPENSES.CATEGORY_ID, ' +
            'EXPENSES.PERIOD_YEAR, EXPENSES.PERIOD_MONTH, EXPENSES.AMOUNT, ' +
            'EXPENSES.REFERENCE, EXPENSES.DUE_DATE, EXPENSES.PAID, EXPENSES.PAYMENT_DATE, ' +
            'EXPENSES.PAYMENT_TYPE, EXPENSES.PAYMENT_REFERENCE, EXPENSES.PAID_AMOUNT, ' +
            'SUPPLIER.COMPANY_NAME From EXPENSE_CATEGORY Inner Join EXPENSES ' +
            'On EXPENSES.CATEGORY_ID = EXPENSE_CATEGORY.ID Inner Join SUPPLIER ' +
            'On EXPENSES.SUPPLIER_ID = SUPPLIER.ID Order By PERIOD_YEAR, PERIOD_MONTH, NAME');
    Open;
  end;
  SetSelectByCategory();
  SetDateVisible();
  chkDateAll.Checked := True;
  SetPhotographerVisible(False);
  SetOverDueOptions(False);
end;

procedure TfmReports.rbLostProfitClick(Sender: TObject);
begin
  SetSelectByCategory(False);
  SetDateVisible();
  chkDateAll.Visible := False;
  SetPhotographerVisible(False);
  SetOverDueOptions(False);
end;

procedure TfmReports.rbMonthlyPaymentClick(Sender: TObject);
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO, INVOICE.INVOICE_DATE,' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, ' +
            'INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_DETAILS, INVOICE.AMOUNT_RECEIVED, INVOICE.PUBLISH_ON ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Where Paid is not null Order By INVOICE.PAYMENT_RECEIVED_DATE, CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO');
    Open;
  end;
  SetSelectByCategory();
  SetDateVisible();
  chkDateAll.Checked := True;
  SetPhotographerVisible(False);
  SetOverDueOptions(False);
end;

procedure TfmReports.PrepareExpensesListData;
var
  tmpSQL: string;
begin
  with DataModule1.qryReport do
  begin
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    if chkDateAll.Checked or chkSelectByAll.Checked then
      SQL.Add('Where');
    if not chkDateAll.Checked then
      SQL.Add(' EXPENSES.DUE_DATE>=:DateFrom ' +
              'And EXPENSES.DUE_DATE <=:DateTo');
    if not chkSelectByAll.Checked then
    begin
      if not chkDateAll.Checked then
        SQL.Add(' And ');
      SQL.Add(' EXPENSES.CATEGORY_ID=:CategoryID');
    end;
    SQL.Add('Order By PERIOD_YEAR, PERIOD_MONTH, NAME');
    if Params.FindParam('DateFrom') <> nil then
    begin
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
    end;
    if Params.FindParam('CategoryID') <> nil then
      Params.ParamByName('CategoryID').AsInteger := VarToInt(dbcbSelectBy.KeyValue);
    if not Prepared then
      Prepare;
    if not Active then
      Open;
  end;
  frxReport1.LoadFromFile('ExpensesList.fr3');
end;

procedure TfmReports.rbPaymentReceivedClick(Sender: TObject);
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO, INVOICE.INVOICE_DATE, ' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, ' +
            'INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_DETAILS, INVOICE.AMOUNT_RECEIVED, INVOICE.PUBLISH_ON ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Where Paid is not null Order By CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO, INVOICE.INVOICE_DUE_DATE');
    Open;
  end;
  SetSelectByCompany();
  SetDateVisible();
  chkDateAll.Checked := True;
  SetPhotographerVisible(False);
  SetOverDueOptions(False);
end;

procedure TfmReports.PreparePaymentReceivedData;
var
  tmpSQL: string;
  myFieldName: string;
begin
  with DataModule1.qryReport do
  begin
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    SQL.Add('Where PAID is not null ');
    case RadioGroup1.ItemIndex of
      0: myFieldName := 'INVOICE.DATE_OF_SHOOT';
      1: myFieldName := 'INVOICE.INVOICE_DATE';
      2: myFieldName := 'INVOICE.PAYMENT_RECEIVED_DATE';
    end;

    if not chkDateAll.Checked then
      SQL.Add(' And ' + myFieldName + '>=:DateFrom ' +
              'And ' + myFieldName + ' <=:DateTo');
    if not chkSelectByAll.Checked then
      SQL.Add(' And INVOICE.CUSTOMER_ID=:CustomerID');
    SQL.Add('Order By ' + myFieldName + ', CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO');
    if Params.FindParam('DateFrom') <> nil then
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
    if Params.FindParam('DateTo') <> nil then
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
    if Params.FindParam('CustomerID') <> nil then
      Params.ParamByName('CustomerID').AsInteger := VarToInt(dbcbSelectBy.KeyValue);
    if not Prepared then
      Prepare;
    if not Active then
      Open;
  end;
  frxReport1.LoadFromFile('PaymentReceived.fr3');
end;

procedure TfmReports.SetSelectByCategory(Visible: boolean);
begin
  lblSelectBy.Visible := Visible;
  chkSelectByAll.Visible := Visible;
  dbcbSelectBy.Visible := lblSelectBy.Visible and not chkSelectByAll.Visible;
  lblSelectBy.Caption := '&Category';
  dbcbSelectBy.ListField := '';
  dbcbSelectBy.KeyField := '';
  dbcbSelectBy.ListSource := DataModule1.dsExpenseCategory;
  dbcbSelectBy.ListField := 'DSC';
  dbcbSelectBy.KeyField := 'ID';
end;

procedure TfmReports.SetDateVisible(Visible: boolean);
begin
  Label3.Visible := Visible;
  chkDateAll.Visible := Visible;
  Visible := not chkDateAll.Checked and chkDateAll.Visible;
  lblDateFrom.Visible := Visible;
  lblDateTo.Visible := Visible;
  dtDateFrom.Visible := Visible;
  dtDateTo.Visible := Visible;
end;

procedure TfmReports.rbPaymentAgingClick(Sender: TObject);
begin
  SetSelectByCompany();
  SetOverDueOptions(False);
end;

procedure TfmReports.rbOverDueDetailsClick(Sender: TObject);
begin
  chkShowPaidAmount.Visible := rbOverDueSummary.Checked;
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select CUSTOMERS.*, INVOICE.INVOICE_NO, INVOICE."FOR", INVOICE.FOR_PRODUCT, ' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, ' +
            'INVOICE.INVOICE_DATE, INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_REFERENCE, INVOICE.AMOUNT_RECEIVED, INVOICE.PUBLISH_ON ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Where Paid is null And INVOICE.INVOICE_DUE_DATE <= CURRENT_DATE ' +
            'Order By CUSTOMERS.COMPANY_NAME, INVOICE.DATE_OF_SHOOT, INVOICE.INVOICE_NO');
    Open;
  end;
end;

procedure TfmReports.rbOverDueListClick(Sender: TObject);
begin
  chkShowPaidAmount.Visible := rbOverDueSummary.Checked;
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select CUSTOMERS.*, INVOICE.INVOICE_NO, INVOICE."FOR", INVOICE.FOR_PRODUCT, ' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, ' +
            'INVOICE.INVOICE_DATE, INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_REFERENCE, INVOICE.AMOUNT_RECEIVED, INVOICE.PUBLISH_ON ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Where Paid is null And INVOICE.INVOICE_DUE_DATE <= CURRENT_DATE ' +
            'Order By CUSTOMERS.COMPANY_NAME, INVOICE.DATE_OF_SHOOT, INVOICE.INVOICE_NO');
    Open;
  end;
end;

procedure TfmReports.rbOverDuePaymentClick(Sender: TObject);
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    SQL.Add('Select CUSTOMERS.*, INVOICE.INVOICE_NO, INVOICE."FOR", INVOICE.FOR_PRODUCT, ' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, ' +
            'INVOICE.INVOICE_DATE, INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_REFERENCE, INVOICE.AMOUNT_RECEIVED, INVOICE.PUBLISH_ON ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Where Paid is null And INVOICE.INVOICE_DUE_DATE <= CURRENT_DATE ' +
            'Order By CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO, INVOICE.INVOICE_DUE_DATE');
    Open;
  end;
  SetSelectByCompany();
  SetDateVisible(True);
  chkDateAll.Checked := False;
  SetPhotographerVisible(False);
  SetOverDueOptions(True);
end;

procedure TfmReports.rbOverDueSummaryClick(Sender: TObject);
begin
  chkShowPaidAmount.Visible := rbOverDueSummary.Checked;
end;

procedure TfmReports.PrepareOverDuePaymentData;
const
  PaidIsNull = ' And Paid is Null';
var
  tmpSQL, s: string;
  sReportName: string;
  FieldName: string;
begin
  case RadioGroup1.ItemIndex of
    0: FieldName := 'DATE_OF_SHOOT';
    1: FieldName := 'INVOICE_DATE';
    2: FieldName := 'PAYMENT_RECEIVED_DATE';
  end;
  s := '';
  with DataModule1.qryReport do
  begin
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    if not rbOverDueSummary.Checked then begin
      tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
      tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
      SQL.Add(tmpSQL);
      SQL.Add('Where Paid is null And INVOICE.INVOICE_DUE_DATE <= CURRENT_DATE');
      if not chkDateAll.Checked then
        SQL.Add(' And INVOICE.' + FieldName+ ' >= :DateFrom And INVOICE.' + FieldName+ ' <= :DateTo');
      if not chkSelectByAll.Checked then
        SQL.Add(' And INVOICE.CUSTOMER_ID=:CustomerID');
      SQL.Add('Order By CUSTOMERS.COMPANY_NAME');
    end else begin
      SQL.Add('Select CUSTOMERS.COMPANY_NAME, Sum(IIF(INVOICE.TOTAL_AMOUNT is null, 0, INVOICE.TOTAL_AMOUNT)) As TOTAL_AMOUNT, ' +
              'Sum(IIF(INVOICE.AMOUNT_RECEIVED is null, 0, INVOICE.AMOUNT_RECEIVED)) As PAID_AMOUNT, ' +
              '(Sum(IIF(INVOICE.TOTAL_AMOUNT is null, 0, INVOICE.TOTAL_AMOUNT)) - Sum(IIF(INVOICE.AMOUNT_RECEIVED is null, 0, INVOICE.AMOUNT_RECEIVED))) As UNPAID_AMOUNT ' +
              'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
              'Where INVOICE.' + FieldName+ ' >= :DateFrom And INVOICE.' + FieldName+ ' <= :DateTo And INVOICE.INVOICE_DUE_DATE <= CURRENT_DATE ');
      if not chkSelectByAll.Checked then
        SQL.Add(' And INVOICE.CUSTOMER_ID=:CustomerID');
      if not chkShowPaidAmount.Checked then
        SQL.Add(PaidIsNull);
      SQL.Add('Group By CUSTOMERS.COMPANY_NAME Order By CUSTOMERS.COMPANY_NAME');
    end;
    if not rbOverDueSummary.Checked then
      case rgSortOrder.ItemIndex of
        0: SQL.Add(', INVOICE.INVOICE_NO');
        1: SQL.Add(', INVOICE.FOR_PRODUCT, INVOICE.INVOICE_NO, INVOICE.DATE_OF_SHOOT');
        2: SQL.Add(', INVOICE.INVOICE_DATE, INVOICE.FOR_PRODUCT, INVOICE.INVOICE_NO');
      end;
    if Assigned(Params.FindParam('CustomerID')) then
      Params.ParamByName('CustomerID').Value := dbcbSelectBy.KeyValue;
    if Assigned(Params.FindParam('DateFrom')) then
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
    if Assigned(Params.FindParam('DateTo')) then
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
    if not Prepared then
      Prepare;
    if not Active then
      Open;
  end;
  if rbOverDueList.Checked then
    sReportName := 'OverDuePaymentList.fr3';
  if rbOverDueDetails.Checked then
    sReportName := 'OverDuePayment.fr3';
  if rbOverDueSummary.Checked then
    if chkShowPaidAmount.Checked then
      sReportName := 'OverDuePaymentSummaryWithPaid.fr3'
    else
      sReportName := 'OverDuePaymentSummary.fr3';
  frxReport1.LoadFromFile(sReportName);
end;

procedure TfmReports.SetPhotographerVisible(Visible: Boolean);
begin
  chkPhotographerAll.Visible := Visible;
  lblPhotographer.Visible := Visible;
  dbcbPhotographer.Visible := not chkPhotographerAll.Checked;
  chkWithOverDueOnly.Visible := Visible;
end;

procedure TfmReports.SetOverDueOptions(Visible: Boolean);
begin
  gbOverDueOptions.Visible := Visible;
end;

procedure TfmReports.PrepareLostProfitData;
var
  i: integer;
begin
  with DataModule1.qryReport do
  begin
    SQL.Clear;
    Params.Clear;
    SQL.Add('Select EXPENSE_CATEGORY.NAME, Sum(EXPENSES.AMOUNT) as Amount ' +
            'From EXPENSES Inner Join EXPENSE_CATEGORY On ' +
            'EXPENSES.CATEGORY_ID = EXPENSE_CATEGORY.ID Where ' +
            'EXPENSES.PERIOD_YEAR=:Year ' +
            'Group By EXPENSE_CATEGORY.NAME ');
    Params.ParamByName('Year').AsInteger := YearOf(dtDateFrom.Date);
    if not Prepared then
      Prepare;
    Open;
    for i := 0 to RecordCount - 1 do
    begin
      ExpensesList.Add(Format('%s=%.2f', [FieldByName('Name').AsString, FieldByName('Amount').AsFloat]));
      Next;
    end;
  end;
  frxReport1.LoadFromFile('LostProfit.fr3');
end;

procedure TfmReports.PrepareMonthlyPaymentData;
begin
  with DataModule1.qryReport do
  begin
    Close;
    SQL.Clear;
    Params.Clear;
    SQL.Add('Select CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO, ' +
            'INVOICE.INVOICE_DUE_DATE, INVOICE.TOTAL_AMOUNT, INVOICE.DATE_OF_SHOOT, ' +
            'INVOICE.PAYMENT_RECEIVED_DATE, INVOICE.PAYMENT_TYPE, ' +
            'INVOICE.PAYMENT_DETAILS, INVOICE.AMOUNT_RECEIVED ' +
            'From INVOICE Inner Join CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
            'Where Paid is not null AND INVOICE.PAYMENT_RECEIVED_DATE >= :DateFrom And INVOICE.PAYMENT_RECEIVED_DATE <= :DateTo ' +
            'Order By INVOICE.PAYMENT_RECEIVED_DATE, CUSTOMERS.COMPANY_NAME, INVOICE.INVOICE_NO');
    if Assigned(Params.FindParam('DateFrom')) then
      Params.ParamByName('DateFrom').AsDate := dtDateFrom.Date;
    if Assigned(Params.FindParam('DateTo')) then
      Params.ParamByName('DateTo').AsDate := dtDateTo.Date;
    if not Prepared then
      Prepare;
    Open;
  end;
  frxReport1.LoadFromFile('MonthlyPayment.fr3');
end;

end.
